if [ -n "$1" ]; then
    data_date=$1
else
  data_date=`date -d '-1 days' +%F`
fi

tmp_dwd_ads_log_parse="
DROP TABLE IF EXISTS jtp_app_warehouse.tmp_dwd_ads_log_parse;
CREATE TABLE IF NOT EXISTS jtp_app_warehouse.tmp_dwd_ads_log_parse
AS
SELECT
    split(parse_url('https://www.baidu.com' || request_url,'PATH'),'/')[2] AS platform_alias_en
    ,split(parse_url('https://www.baidu.com' || request_url,'PATH'),'/')[3] AS event_type
    ,parse_url('https://www.baidu.com' || request_url,'QUERY','t') AS event_time
    ,parse_url('https://www.baidu.com' || request_url,'QUERY','id') AS ads_id
    ,parse_url('https://www.baidu.com' || request_url,'QUERY','ip') AS client_ip
    ,parse_url('https://www.baidu.com' || request_url,'QUERY','device_id') AS client_device_id
    ,parse_url('https://www.baidu.com' || request_url,'QUERY','os_type') AS client_os_type
    ,reflect('java.net.URLDecoder','decode',parse_url('https://www.baidu.com' || request_url,'QUERY','ua'),'utf-8') AS client_user_agent
FROM jtp_app_warehouse.ods_ads_log_info
WHERE dt = '${data_date}';
"
tmp_dwd_ads_log_dim="
DROP TABLE IF EXISTS jtp_app_warehouse.tmp_dwd_ads_log_dim;
CREATE TABLE IF NOT EXISTS jtp_app_warehouse.tmp_dwd_ads_log_dim
AS
SELECT
    t2.id,
    t2.ad_id,
    t2.ad_name,
    t2.ad_group_id,
    t2.product_id,
    t2.product_name,
    t2.product_price,
    t2.material_id,
    t2.material_url,
    t2.platform_id,
    t2.platform_alias_en,
    t2.platform_alias_zh,
    t1.event_type,
    t1.event_time,
    t1.client_ip,
    t1.client_device_id,
    t1.client_os_type,
    t1.client_user_agent,
    t2.create_time,
    t2.cancel_time
FROM jtp_app_warehouse.tmp_dwd_ads_log_parse t1
LEFT JOIN jtp_app_warehouse.dim_ads_platform_info_full t2 ON t1.ads_id = t2.ad_id
WHERE t2.dt = '${data_date}';
"
tmp_dwd_ads_log_region="
DROP TABLE IF EXISTS jtp_app_warehouse.tmp_dwd_ads_log_region;
CREATE TABLE IF NOT EXISTS jtp_app_warehouse.tmp_dwd_ads_log_region
AS
SELECT
    id,
    ad_id,
    ad_name,
    ad_group_id,
    product_id,
    product_name,
    product_price,
    material_id,
    material_url,
    platform_id,
    platform_alias_en,
    platform_alias_zh,
    event_type,
    event_time,
    client_ip,
    client_device_id,
    client_os_type,
    client_user_agent,
    region_max['country'] AS client_country,
    region_max['area'] AS client_area,
    region_max['province'] AS client_province,
    region_max['city'] AS client_city,
    create_time,
    cancel_time
FROM (
         SELECT
             *,jtp_app_warehouse.ip_to_region(client_ip) AS region_max
         FROM jtp_app_warehouse.tmp_dwd_ads_log_dim
     )t1;
"
tmp_dwd_ads_log_ua="
DROP TABLE IF EXISTS jtp_app_warehouse.tmp_dwd_ads_log_ua;
CREATE TABLE IF NOT EXISTS jtp_app_warehouse.tmp_dwd_ads_log_ua
AS
SELECT
    id,
    ad_id,
    ad_name,
    ad_group_id,
    product_id,
    product_name,
    product_price,
    material_id,
    material_url,
    platform_id,
    platform_alias_en,
    platform_alias_zh,
    event_type,
    event_time,
    client_ip,
    client_device_id,
    client_os_type,
    client_user_agent,
    client_country,
    client_area,
    client_province,
    client_city,
    ua_map['os_version'] AS client_os_version,
    ua_map['browser'] AS client_browser,
    ua_map['version'] AS client_browser_version,
    create_time,
    cancel_time
FROM (
         SELECT
             *,jtp_app_warehouse.ua_to_region(client_user_agent) AS ua_map
         FROM jtp_app_warehouse.tmp_dwd_ads_log_region
     )t1;
"
dwd_ads_event_log_full="
WITH t1 AS (
    SELECT
        DISTINCT client_ip
    FROM (
             SELECT
                 client_ip
                  ,event_time
                  ,ad_id
                  ,count(1) OVER(PARTITION BY client_ip,ad_id ORDER BY CAST(event_time AS BIGINT)
                 RANGE BETWEEN 300000 PRECEDING AND CURRENT ROW ) AS cnt
             FROM jtp_app_warehouse.tmp_dwd_ads_log_ua
         )
    WHERE cnt > 100
    UNION
    SELECT
        DISTINCT client_ip
    FROM (
             SELECT
                 client_ip, ad_id, event_time, next_event_time
                  ,(next_event_time - event_time) AS time_diff
             FROM (
                      SELECT
                          client_ip,
                          ad_id,
                          event_time,
                          lead(event_time,1,0) over (PARTITION BY client_ip,ad_id ORDER BY event_time) AS next_event_time
                      FROM jtp_app_warehouse.tmp_dwd_ads_log_ua
                  )t1
         )t2
    GROUP BY client_ip,ad_id,time_diff
    HAVING count(1) > 5
),t2 AS (
    SELECT
        DISTINCT client_device_id
    FROM (
             SELECT
                 client_device_id
                  ,event_time
                  ,ad_id
                  ,count(1) OVER(PARTITION BY client_device_id,ad_id ORDER BY CAST(event_time AS BIGINT)
                 RANGE BETWEEN 300000 PRECEDING AND CURRENT ROW ) AS cnt
             FROM jtp_app_warehouse.tmp_dwd_ads_log_ua
         )
    WHERE cnt > 100
    UNION
    SELECT
        DISTINCT client_device_id
    FROM (
             SELECT
                 client_device_id, ad_id, event_time, next_event_time
                  ,(next_event_time - event_time) AS time_diff
             FROM (
                      SELECT
                          client_device_id,
                          ad_id,
                          event_time,
                          lead(event_time,1,0) over (PARTITION BY client_device_id,ad_id ORDER BY event_time) AS next_event_time
                      FROM jtp_app_warehouse.tmp_dwd_ads_log_ua
                  )t1
         )t2
    GROUP BY client_device_id,ad_id,time_diff
    HAVING count(1) > 5
)
INSERT OVERWRITE TABLE jtp_app_warehouse.dwd_ads_event_log_full PARTITION (dt = '${data_date}')
SELECT
    t3.id,
    t3.ad_id,
    t3.ad_name,
    t3.ad_group_id,
    t3.product_id,
    t3.product_name,
    t3.product_price,
    t3.material_id,
    t3.material_url,
    t3.platform_id,
    t3.platform_alias_en,
    t3.platform_alias_zh,
    t3.event_type,
    CAST(t3.event_time AS BIGINT),
    t3.client_ip,
    t3.client_device_id,
    t3.client_os_type,
    t3.client_user_agent,
    t3.client_country,
    t3.client_area,
    t3.client_province,
    t3.client_city,
    t3.client_os_version,
    t3.client_browser,
    t3.client_browser_version,
    t3.create_time,
    t3.cancel_time,
    t1.client_ip IS NULL AND t2.client_device_id IS NULL AS is_invalid_traffic
FROM jtp_app_warehouse.tmp_dwd_ads_log_ua t3
LEFT JOIN t1 ON t3.client_ip = t1.client_ip
LEFT JOIN t2 ON t3.client_device_id = t2.client_device_id;
"
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "
${tmp_dwd_ads_log_parse}
${tmp_dwd_ads_log_dim}
${tmp_dwd_ads_log_region}
${tmp_dwd_ads_log_ua}
${dwd_ads_event_log_full}
"